package com.augurit.gis.common.dao;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.augurit.gis.common.util.Item;


/**
 * <p>Title: 执行一般性的SQL语句的DAO</p>
 * <p>Description: 执行一般性的SQL语句,支持多数据源</p>
 * <p>Company: 广州奥格智能科技有限公司</p>
 * @author 沈坚丰
 * @date 2009-05-04
 */
public class CommonDAO {
	
	/**
	 * SDE数据源
	 */
	public static final String dataSourceSDE = "sde";
	
	/**
	 * MIS数据源
	 */
	public static final String dataSourceMIS = "mis";
	
	/**
	 * 当前DAO的数据源名称
	 */
	private String dataSourceName = null;
	
	public CommonDAO() {
		this(dataSourceSDE);
	}
	
	/**
	 * 改变数据源名称
	 * @param dataSourceName
	 */
	public void changeDataSource(String dataSourceName){
		setDataSourceName(dataSourceName);
	}
	
	/**
	 * 设置数据源名称
	 * @param dataSourceName
	 */
	public void setDataSourceName(String dataSourceName){
		if(dataSourceName==null)dataSourceName=dataSourceSDE;
		if(!dataSourceName.equals(dataSourceSDE)
		 &&!dataSourceName.equals(dataSourceMIS)){
			System.out.println("数据源参数错误:dataSourceName="+dataSourceName);
			dataSourceName = dataSourceSDE;
		}
		this.dataSourceName = dataSourceName;
	}
	
	public CommonDAO(String dataSourceName) {
		setDataSourceName(dataSourceName);
	}

	/**
	 * 获得数据连接
	 * @return Connection对象
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException{
		Connection connection = null;
		System.out.println("Creating connection");
		if(dataSourceName!=null && 
		   dataSourceName.equals(dataSourceMIS)){
			System.out.println("Creating mis connection");
			connection = ConnectionFactory.getMisConnection();
		}else{
			System.out.println("Creating gis connection");
			connection = ConnectionFactory.getSdeConnection();
		}
		System.out.println(connection.toString());
		return connection;
	}
	
	/**
	 * 获得数据连接
	 * @return Connection对象
	 * @throws SQLException
	 */
	public Connection getSLGCConnection() throws SQLException{
		Connection connection = null;

		connection = ConnectionFactory.getSLGCConnection();

		return connection;
	}
	
	/**
	 * 普通查询
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List commonSearch(String sql,String methodName,Connection connection) throws SQLException{
		List list = new ArrayList();
		boolean createFlag = false;
		PreparedStatement preparedStatement = null;
		try {
			if(connection==null){
				connection = getConnection();
				createFlag = true;
			}
			Statement statement=  connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			Method method = this.getClass().getMethod(methodName, ResultSet.class);
			list = (List) method.invoke(this, set);
//			list = resultSetToList(set);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(preparedStatement!=null){
				preparedStatement.close();
			}
			if(connection!=null&&createFlag){
				connection.close();
			}
		}
		return list;
	}

	/**
	 * 普通查询
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List commonSearch(String sql,Connection connection) throws SQLException{
		List list = new ArrayList();
		boolean createFlag = false;
		PreparedStatement preparedStatement = null;
		try {
			if(connection==null){
				connection = getConnection();
				createFlag = true;
			}
			System.out.println(connection.toString());
			Statement statement=  connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			list = resultSetToList(set);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(preparedStatement!=null){
				preparedStatement.close();
			}
			if(connection!=null&&createFlag){
				connection.close();
			}
		}
		return list;
	}
	
	public List commonSearch2(String sql,Connection connection) throws SQLException{
		List list = new ArrayList();
		boolean createFlag = false;
		PreparedStatement preparedStatement = null;
		try {
			if(connection==null){
				connection = getConnection();
				createFlag = true;
			}
			System.out.println(connection.toString());
			Statement statement=  connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			list = resultSetToList2(set);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(preparedStatement!=null){
				preparedStatement.close();
			}
			if(connection!=null&&createFlag){
				connection.close();
			}
		}
		return list;
	}
	
	/**
	 * 普通查询
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List<String> SLGCSearch(String sql,Connection connection) throws SQLException{
		List<String> arrays = new ArrayList<String>();
		PreparedStatement preparedStatement = null;
		try {
		
			connection = getSLGCConnection();
			
			Statement statement=  connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			
			if(set==null)
				return null;

				while(set.next()){
					for(int i=11;i>=1;i--){
					String value = set.getString(i);
					if(value==null){value="";}
					arrays.add(value);
					System.out.println(value);
					}
				}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(preparedStatement!=null){
				preparedStatement.close();
			}
			if(connection!=null){
				connection.close();
			}
		}
		return arrays;
	}
	
	/**
	 * 将查询结果集转化为VO对象
	 * @param set
	 * @return
	 * @throws SQLException
	 */
	public List resultSetToList(ResultSet set) throws SQLException{
		// TODO Auto-generated method stub
		return null;
	}
	
	public List resultSetToList2(ResultSet set) throws SQLException{
		// TODO Auto-generated method stub
		return null;
	}
	
	/**
	 * 执行查询SQL，并将查询集转化为String数组
	 * @param sql
	 * @param connection
	 * @return String数组
	 * @throws SQLException
	 */
	public String[] getStringArray(String sql, Connection connection)
			throws SQLException {
		List<String> list = getStringList(sql, connection);
		String[] array = null;
		if(!list.isEmpty()){
			array = new String[list.size()];
			list.toArray(array);
		}
		return array;
	}

	/**
	 * 执行查询SQL，并将查询集转化为String列表
	 * @param sql
	 * @param connection
	 * @return String列表
	 * @throws SQLException
	 */
	public List<String> getStringList(String sql, Connection connection)
			throws SQLException {
		boolean createFlag = false;
		ResultSet resultSet = null;
		Statement statement = null;
		List<String> list = null;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			list = DataConventor.resultSet2StringList(resultSet);
		} catch (SQLException ex) {
			throw ex;
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
					resultSet = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 执行查询SQL，并将查询集转化为Item列表
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public List<Item> getItemList(String sql, Connection connection)
			throws SQLException {
		return getItemList(sql, 2, connection);
	}

	/**
	 * 执行查询SQL，并将查询集转化为Item列表
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public List<Item> getItemList(String sql, int fieldCount,
			Connection connection) throws SQLException {
		boolean createFlag = false;
		ResultSet resultSet = null;
		Statement statement = null;
		List<Item> list = new ArrayList<Item>();
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			// System.out.println("执行sql:" + sql);
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				Item item = new Item();
				item.setKey(resultSet.getString(1));
				item.setValue(resultSet.getString(2));
				if (fieldCount > 2) {
					item.setOther1(resultSet.getString(3));
					if (fieldCount > 3) {
						item.setOther2(resultSet.getString(4));
					}
					if (fieldCount > 4) {
						item.setOther3(resultSet.getString(5));
					}
					if (fieldCount > 5) {
						item.setOther4(resultSet.getString(6));
					}
					if (fieldCount > 6) {
						item.setOther5(resultSet.getString(7));
					}
				}
				list.add(item);
			}
		} catch (SQLException ex) {
			System.out.println("item sql出错:"+sql);
			throw ex;
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
					resultSet = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 执行查询SQL，并将查询集转化为HashMap
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public HashMap getHashMap(String sql, Connection connection)
			throws SQLException {
		List hashMapList = getHashMapList(sql, connection);
		HashMap hashMap = null;
		if (hashMapList.size() != 0) {
			hashMap = (HashMap) hashMapList.get(0);
		}
		return hashMap;
	}

	/**
	 * 执行查询SQL，并将查询集转化为HahsMap列表
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */

	public List getHashMapList(String sql, Connection connection)
			throws SQLException {
		boolean createFlag = false;
		ResultSet resultSet = null;
		Statement statement = null;
		List list = null;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			// System.out.println("执行sql:" + sql);
			resultSet = statement.executeQuery(sql);
			list = DataConventor.resultSet2HashMapList(resultSet);
		} catch (SQLException ex) {
			System.out.println("SQL执行错误:" + sql);
			throw ex;
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
					resultSet = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 执行查询SQL，并将查询集转化为HashMap列表，且HashMap中的value都为String类型
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */

	public List getStringHashMapList(String sql, Connection connection)
			throws SQLException {
		boolean createFlag = false;
		ResultSet resultSet = null;
		Statement statement = null;
		List list = null;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			// System.out.println("执行sql:" + sql);
			resultSet = statement.executeQuery(sql);
			list = DataConventor.resultSet2StringHashMapList(resultSet);
		} catch (SQLException ex) {
			System.out.println("SQL执行错误:" + sql);
			throw ex;
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
					resultSet = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 执行查询SQL，并将查询集转化为HashMap，且HashMap中的value都为String类型
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public HashMap getStringHashMap(String sql, Connection connection)
			throws SQLException {
		List stringHashMapList = getStringHashMapList(sql, connection);
		HashMap stringHashMap = null;
		if (stringHashMapList.size() != 0) {
			stringHashMap = (HashMap) stringHashMapList.get(0);
		}
		return stringHashMap;
	}

	/**
	 * 执行查询：返回ResultSet
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql, Connection connection)
			throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		return resultSet;
	}
	/**
	 * 获得查询结果中的第一条记录的第一个值的字符串形式
	 * 
	 * @param sql
	 * @param connection
	 * @return
	 */
	public String getValue(String sql, Connection connection) {
		return getValue(sql, connection, false);
	}
	
	/**
	 * 获得查询结果中的第一条记录的第一个值，并将该值转化为numberClass类型
	 * @param sql
	 * @param connection
	 * @return
	 */
	public Number getNumberValue(String sql, Connection connection,Class<? extends Number> numberClass){
		String value = getValue(sql, connection);
		if(value!=null){
			if(numberClass.equals(Integer.class)){
				return Integer.valueOf(value);
			}else if(numberClass.equals(Long.class)){
				return Long.valueOf(value);
			}else if(numberClass.equals(Double.class)){
				return Double.valueOf(value);
			}else if(numberClass.equals(Float.class)){
				return Float.valueOf(value);
			}
		}
		return null;
	}
	/**
	 * 获得查询结果中的第一条记录的第一个值的字符串形式
	 * @param sql
	 * @param connection
	 * @param isNumericField
	 * @return
	 */
	public String getValue(String sql, Connection connection,
			boolean isNumericField) {
		Statement statement = null;
		ResultSet resultSet = null;
		String ret = null;
		boolean createFlag = false;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			ResultSetMetaData data = resultSet.getMetaData();
			if (resultSet.next()) {
				String typeName = data.getColumnTypeName(1);
				if (resultSet.getString(1) == null) {
					if (isNumericField)
						ret = "0";
				} else {
					if (typeName.equals("DATE")) {
						ret = resultSet.getDate(1).toString();
					} else {
						ret = resultSet.getString(1);
					}
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
					resultSet = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return ret;
	}

	/**
	 * 批量执行SQL
	 * @param sqls
	 * @param connection
	 * @return
	 */
	public boolean executeBatch(String[] sqls, Connection connection) {
		return executeBatch(sqls, connection,false);
	}

	/**
	 * 批量执行SQL
	 * @param sqls
	 * @param connection
	 * @param showSql:是否显示SQL
	 * @return
	 */
	public boolean executeBatch(String[] sqls, Connection connection, boolean showSql) {
		boolean createFlag = false;
		boolean succFlag = true;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			connection.setAutoCommit(false);
			Statement statement = connection.createStatement();
			for (int i = 0; i < sqls.length; i++) {
				if(showSql){
					System.out.println(sqls[i]);
				}
				statement.addBatch(sqls[i]);
			}
			statement.executeBatch();
			connection.commit();
		} catch (Exception e) {
			succFlag = false;
			System.out.println(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			if (connection != null && createFlag) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return succFlag;
	}

	/**
	 * 执行数据更新
	 * @param sql
	 * @param connection
	 * @return true 成功; false 失败
	 */
	public boolean executeSQL(String sql, Connection connection) {
		Statement statement = null;
		boolean createFlag = false;
		boolean succFlag = true;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			int updateCount = statement.executeUpdate(sql);
			System.out.println("执行sql=" + sql);
			System.out.println("updateCount=" + updateCount);
		} catch (SQLException e) {
			System.err.println("执行更新时发生错误(sql=" + sql + ")");
			succFlag = false;

		} finally {
			try {
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return succFlag;
	}

	/**
	 * 执行数据更新
	 * @param sql
	 * @param connection
	 * @return true 成功; false 失败
	 */
	public int executeUpdate(String sql, Connection connection) {
		Statement statement = null;
		boolean createFlag = false;
		int updateCount = 0;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			updateCount = statement.executeUpdate(sql);
			System.out.println("执行sql=" + sql + ";updateCount=" + updateCount);
		} catch (SQLException e) {
			System.err.println("执行更新时发生错误(sql=" + sql + "):" + e.getMessage());
		} finally {
			try {
				if (statement != null) {
					statement.close();
					statement = null;
				}
				if (connection != null && createFlag) {
					connection.close();
					connection = null;
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return updateCount;
	}
	
	/**
	 * 获得所要执行的SQL的记录数
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public int getResultCount(String sql, Connection connection)
			throws SQLException {
		int count = 0;
		boolean createFlag = false;
		String sqlForCount = "select count(*) from (" + sql + ")";
		Statement statement = null;
		ResultSet set = null;
		try {
			if (connection == null) {
				connection = getConnection();
				createFlag = true;
			}
			statement = connection.createStatement();
			set = statement.executeQuery(sqlForCount);
			if (set.next()) {
				count = set.getInt(1);
			}
		} catch (SQLException e) {
			System.out.println("执行SQL发生错误：" + sql);
			e.printStackTrace();
			throw e;
		} finally {
			try {
				if (set != null) {
					set.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null && createFlag) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}

	/**
	 * 执行分页查询，并得到对应的结果集对象
	 * @param startRowNum 开始记录行号
	 * @param endRowNum 结束记录行号
	 * @param sql
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getDataByRowNum(int startRowNum, int endRowNum,
			String sql, Connection connection) throws SQLException {
		Statement statement = connection.createStatement();

		String finalSql = "select a.*,rownum rn from (" + sql
				+ ") a where rownum<=" + endRowNum;
		finalSql = "select * from (" + finalSql + ") where rn>" + startRowNum;
		ResultSet resultSet = statement.executeQuery(finalSql);
		return resultSet;
	}
	public String buildSQL(int startRowNum, int endRowNum,String sql){
		String finalSql = "select a.*,rownum rn from (" + sql
		+ ") a where rownum<=" + endRowNum;
		finalSql = "select * from (" + finalSql + ") where rn>" + startRowNum;
		return finalSql;
	}

	/**
	 * 执行分页查询，并得到对应的结果集对象
	 * @param sql 
	 * @param count 每页的记录数
	 * @param page 页码
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getDataByPage(String sql, int count, int page,
			Connection connection) throws SQLException {
		if (count < 1) {
			count = 10;
		}
		if (page < 1) {
			page = 1;
		}
		int startRowNum = count * (page - 1);
		int endRowNum = count * page;
		return getDataByRowNum(startRowNum, endRowNum, sql, connection);
	}
}
